﻿using MySql.Data.MySqlClient;
using QLInternet.Connection;
using QLInternet.Model;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;

namespace QLInternet.ViewModel
{
   public  class DangKy
    {
        public int getId(Connect conn, String query)
        {
            int values = 0;
            MySqlDataReader reader = null;
            conn.getConnect();
            MySqlCommand cmd = new MySqlCommand(query, conn.con);
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                values = reader.GetInt32(0);
            }
            reader.Close();
            conn.CloseConnet();
            return values;
        }
        public void loadDataToComboBox(Connect conn, ComboBox cbbx, String query)
        {
            MySqlDataReader reader = null;
            if (!conn.IsOpenning)
                conn.getConnect();
            MySqlCommand cmd = new MySqlCommand(query, conn.con);
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                cbbx.Items.Add(new ComboboxItem { Id = reader.GetInt32(0), Name = reader.GetString(1) });
            }
            reader.Close();
            conn.CloseConnet();
        }


        public void loadModem(Connect conn, ComboBox cbbx, String queryMaModem, String queryModem)
        {
            List<int> list = loadMaModem(conn, queryMaModem);

            for (int i = 0; i < list.Count; i++)
            {
                MySqlDataReader reader = null;
                if (!conn.IsOpenning)
                    conn.getConnect();
                MySqlCommand cmd = new MySqlCommand(queryModem + list.ElementAt(i), conn.con);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    cbbx.Items.Add(new ComboboxItem { Id = reader.GetInt32(0), Name = reader.GetString(1) });
                }
                reader.Close();
                conn.CloseConnet();
            }

        }
        public List<int> loadMaModem(Connect conn, String queryMaModem)
        {
            List<int> maModem = new List<int>();
            MySqlDataReader reader = null;
            if (!conn.IsOpenning)
                conn.getConnect();
            MySqlCommand cmd = new MySqlCommand(queryMaModem, conn.con);
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                maModem.Add(reader.GetInt32(0));
            }
            reader.Close();
            conn.CloseConnet();
            return maModem;

        }


        public ObservableCollection<CustomerViewModel> loadDSKH(Connect conn, String query)
        {
            ObservableCollection<CustomerViewModel> customer = new ObservableCollection<CustomerViewModel>();
            String values = "";
            String tinhTrangmang;
            MySqlDataReader reader = null;
            int i = 0;
            if (!conn.IsOpenning)
                conn.getConnect();
            MySqlCommand cmd = new MySqlCommand(query, conn.con);
            reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                if (reader.GetString(11).Trim().Equals("True"))
                    values = "Trọn gói";
                else
                    values = "Phương thức lưu lượng";
                if (reader.GetInt32(17) == 0)
                    tinhTrangmang = "Bình thường";
                else
                    tinhTrangmang = "Có lỗi";
                customer.Add(new CustomerViewModel
                {
                    STT = (i + 1).ToString(),
                    MaKh = reader.GetInt32(0),
                    HoTen = reader.GetString(1),
                    CMND = reader.GetInt32(2),
                    NgheNghiep = reader.GetString(3),

                    DiaChi = reader.GetString(4),
                    DienThoai = reader.GetInt32(5),
                    Email = reader.GetString(6),
                    DiaChiCaiDat = reader.GetString(7),
                    DiaChiHoaDon = reader.GetString(8),
                    SoLuongTaiKhoan = reader.GetInt32(9),
                    TenNguoiSuDung = reader.GetString(10),
                    TronGoi = values,
                    TenKieuCaiDat = reader.GetString(12),
                    TenModem = reader.GetString(13),
                    TenGoiCuoc = reader.GetString(14),
                    TenTinhTrangSuDung = reader.GetString(15),
                    MaHopDong = reader.GetInt32(16),
                    LoiKiThuat = tinhTrangmang

                });
                i++;
            }
            reader.Close();
            conn.CloseConnet();
            return customer;


        }

        public ObservableCollection<CustomerViewModel> loadDSKHChuaThanhToan(Connect conn, String query)
        {
            ObservableCollection<CustomerViewModel> customer = new ObservableCollection<CustomerViewModel>();
            MySqlDataReader reader = null;
            int i = 0;
            if (!conn.IsOpenning)
                conn.getConnect();
            MySqlCommand cmd = new MySqlCommand(query, conn.con);
            reader = cmd.ExecuteReader();
            DateTime dayDk;
            DateTime dayNow;
            int yearDk;
            int countDay = 0;
            while (reader.Read())
            {
                dayDk = reader.GetDateTime(6);
                dayNow = DateTime.Now;
                yearDk = dayDk.Year;
                if (yearDk != dayNow.Year)
                {
                    if (yearDk % 4 == 0 && yearDk % 100 != 0)
                    {
                        countDay = 366 - dayDk.DayOfYear + dayNow.DayOfYear;
                    }
                    else
                        countDay = 365 - dayDk.DayOfYear + dayNow.DayOfYear;
                }
                else
                    countDay = dayNow.DayOfYear - dayDk.DayOfYear;
                customer.Add(new CustomerViewModel
                {
                    STT = (i + 1).ToString(),
                    MaKh = reader.GetInt32(0),
                    HoTen = reader.GetString(1),
                    DiaChi = reader.GetString(4),
                    CMND = reader.GetInt32(2),
                    DienThoai = reader.GetInt32(3),
                    TongTienThanhToan = reader.GetInt32(5),
                    TongSoNgay = countDay
                });
                i++;
            }
            reader.Close();
            conn.CloseConnet();
            return customer;
        }

        public ObservableCollection<CustomerViewModel> loadDangKy(Connect conn, String query)
        {
            ObservableCollection<CustomerViewModel> customer = new ObservableCollection<CustomerViewModel>();
            String values = "";
            MySqlDataReader reader = null;
            int i = 0;
            if (!conn.IsOpenning)
                conn.getConnect();
            MySqlCommand cmd = new MySqlCommand(query, conn.con);
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                if (reader.GetString(11).Trim().Equals("True"))
                    values = "Trọn gói";
                else
                    values = "Phương thức lưu lượng";
                customer.Add(new CustomerViewModel
                {
                    STT = (i + 1).ToString(),
                    MaKh = reader.GetInt32(0),
                    HoTen = reader.GetString(1),
                    CMND = reader.GetInt32(2),
                    NgheNghiep = reader.GetString(3),
                    DiaChi = reader.GetString(4),
                    DienThoai = reader.GetInt32(5),
                    Email = reader.GetString(6),
                    DiaChiCaiDat = reader.GetString(7),
                    DiaChiHoaDon = reader.GetString(8),
                    SoLuongTaiKhoan = reader.GetInt32(9),
                    TenNguoiSuDung = reader.GetString(10),
                    TenKieuCaiDat = reader.GetString(12),
                    TenModem = reader.GetString(13),
                    TenGoiCuoc = reader.GetString(14),
                    TronGoi = values
                });
                i++;
            }
            reader.Close();
            conn.CloseConnet();
            return customer;
        }
        public int getIdAutoinCrement(Connect conn, String str)
        {
            int values = 0;
            String query = "select auto_increment from information_schema.TABLES where TABLE_NAME = '" + str + "'";
            MySqlDataReader reader = null;
            conn.getConnect();
            MySqlCommand cmd = new MySqlCommand(query, conn.con);
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                values = reader.GetInt32(0);
            }
            reader.Close();
            conn.CloseConnet();
            return values;
        }

        public String getValues(Connect conn, String query)
        {
            String values = "";
            MySqlDataReader reader = null;
            conn.getConnect();
            MySqlCommand cmd = new MySqlCommand(query, conn.con);
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                values = reader.GetString(0);
            }
            reader.Close();
            conn.CloseConnet();
            return values;
        }


        public void inserIntoDataTable(Connect conn, String query)
        {
            conn.getConnect();
            try
            {
                MySqlCommand cmd = new MySqlCommand(query, conn.con);
                cmd.ExecuteNonQuery();
                conn.CloseConnet();
            }
            catch
            {
                MessageBox.Show("Lỗi truy vấn ", "Error");
            }

        }


        public void inSertCustomer(Connect conn, CustomerViewModel customer)
        {

            String queryKh = "INSERT INTO KHACHHANG(HoTen,CMND,NgheNghiep,DiaChi,DienThoai,Email)" +
                " VALUES ('" + customer.HoTen + "','" + customer.CMND + "','" + customer.NgheNghiep + "','" + customer.DiaChi + "','" + customer.DienThoai + "','" + customer.Email + "')";
            inserIntoDataTable(conn, queryKh);
            int maKh = getIdAutoinCrement(conn, "KHACHHANG") - 1;
            DateTime d = DateTime.Now;
            String sqlmctcd = "select MaChiTiet from CHITIETKIEUCAIDAT where MaKieuCaiDat = '" + Int32.Parse(customer.TenKieuCaiDat) + "' and MaModem = '" + Convert.ToInt32(customer.TenModem) + "'";
            int maCtkcd = getId(conn, sqlmctcd);
            Console.WriteLine("Ma kieu cai dat 2 = " + customer.TenKieuCaiDat);
            Console.WriteLine("ma modem 2 = " + customer.TenModem);
            Console.WriteLine("ma chi tiet khach hang = " + maCtkcd);
            String sql = "insert into DANGKY(MaKhachHang,NgayDangKy,DiaChiCaiDat,DiaChiHoaDon,SoLuongTaiKhoan,TenNguoiSuDung,MaGoiCuoc,TronGoi,MaChiTietKieuCaiDat)" +
                " values('" + maKh + "','" + d.ToString("yyyy/MM/dd") + "','" + customer.DiaChiCaiDat + "','" + customer.DiaChiHoaDon + "','" + customer.SoLuongTaiKhoan + "','" + customer.TenNguoiSuDung + "','" + Convert.ToInt32(customer.TenGoiCuoc) + "','" + Convert.ToInt32(customer.TronGoi) + "','" + maCtkcd + "')";
            inserIntoDataTable(conn, sql);
        }

        public void deleteCustomer(Connect conn, CustomerViewModel ctm)
        {
            String query1 = "delete from khachhang where MaKhachHang = " + ctm.MaKh;
            deleteRowInDataTable(conn, query1);
            String sqlMaDangKy = "select MaDangKy from dangky where MaKhachHang = " + ctm.MaKh;
            int maDangKy = getId(conn, sqlMaDangKy);
            String query2 = "delete from dangky where MaDangKy = " + maDangKy;
            deleteRowInDataTable(conn, query2);

        }

        public void deleteRowInDataTable(Connect conn, String query)
        {
            conn.getConnect();
            try
            {
                MySqlCommand cmd = new MySqlCommand(query, conn.con);
                cmd.ExecuteNonQuery();
                conn.CloseConnet();
            }
            catch
            {
                MessageBox.Show("Lỗi truy vấn ", "Error");
            }
        }

        public void deleteCustomerHuyDk(Connect conn, CustomerViewModel ctm)
        {
            String query1 = "delete from khachhang where MaKhachHang = " + ctm.MaKh;
            deleteRowInDataTable(conn, query1);
            String sqlMaDangKy = "select MaDangKy from dangky where MaKhachHang = '" + ctm.MaKh + "'";
            int maDangKy = getId(conn, sqlMaDangKy);
            String query2 = "delete from dangky where MaDangKy = '" + maDangKy + "'";
            deleteRowInDataTable(conn, query2);
        }


        public List<int> getListMaKhQuahan(Connect conn)
        {
            List<int> list = new List<int>();
            String query = "select MaKhachHang , ngaydangky from dangky";
            MySqlDataReader reader = null;
            conn.getConnect();
            MySqlCommand cmd = new MySqlCommand(query, conn.con);
            reader = cmd.ExecuteReader();
            DateTime dayNow = DateTime.Now;
            int yearOfNow = dayNow.Year;
            int sttDayNow = dayNow.DayOfYear;
            int countDay = 0;
            while (reader.Read())
            {
                int yearOfNgDk = reader.GetDateTime(1).Year;
                if (reader.GetDateTime(1).Year != dayNow.Year)
                {

                    if (reader.GetDateTime(1).Year % 4 == 0 && reader.GetDateTime(1).Year % 100 != 0)
                    {
                        countDay = 366 - reader.GetDateTime(1).DayOfYear + sttDayNow;
                    }
                    else
                        countDay = 365 - reader.GetDateTime(1).DayOfYear + sttDayNow;
                }
                else
                    countDay = sttDayNow - reader.GetDateTime(1).DayOfYear;

                if (countDay > 30 || (yearOfNow - yearOfNgDk) >= 2)
                    list.Add(reader.GetInt32(0));

            }
            reader.Close();
            conn.CloseConnet();
            return list;
        }

        public void deleteCustomerQuaHanThanhToan(Connect conn)
        {

            List<int> list = getListMaKhQuahan(conn);

            for (int i = 0; i < list.Count; i++)
            {
                String query1 = "delete from khachhang where MaKhachHang = " + list.ElementAt(i);
                deleteRowInDataTable(conn, query1);
                String sqlMaDangKy = "select MaDangKy from dangky where MaKhachHang = " + list.ElementAt(i);
                int maDangKy = getId(conn, sqlMaDangKy);
                String query2 = "delete from dangky where MaDangKy = " + maDangKy;
                deleteRowInDataTable(conn, query2);
            }

        }

        public void updateCustomer(Connect conn, CustomerViewModel ctm)
        {
            String sqlKh = "update khachhang set HoTen = '" + ctm.HoTen + "',CMND = '" + ctm.CMND + "',NgheNghiep = '" + ctm.NgheNghiep + "',DiaChi = '" + ctm.DiaChi + "',DienThoai = '" + ctm.DienThoai + "',Email = '" + ctm.Email + "' where MaKhachHang = '" + ctm.MaKh + "'";
            Console.WriteLine("Ma kh = " + ctm.MaKh);
            updateRowInDataTable(conn, sqlKh);
            String sqlmctcd = "select MaChiTiet from CHITIETKIEUCAIDAT where MaKieuCaiDat = '" + Int32.Parse(ctm.TenKieuCaiDat) + "' and MaModem = '" + Convert.ToInt32(ctm.TenModem) + "'";
            int maCtkcd = getId(conn, sqlmctcd);
            String sqlMadk = "select MaDangKy from DangKy where MaKhachHang = " + ctm.MaKh;
            int maDk = getId(conn, sqlMadk);
            String sqlDk = "update dangky set DiaChiCaiDat = '" + ctm.DiaChiCaiDat + "',DiaChiHoaDon = '" + ctm.DiaChiHoaDon + "',SoLuongTaiKhoan = '" + ctm.SoLuongTaiKhoan + "',TenNguoiSuDung = '" + ctm.TenNguoiSuDung + "',MaGoiCuoc = '" + Convert.ToInt32(ctm.TenGoiCuoc) + "',TronGoi = '" + Convert.ToInt32(ctm.TronGoi) + "',MaChiTietKieuCaiDat = '" + maCtkcd + "' where MaDangky = '" + maDk + "'";
            updateRowInDataTable(conn, sqlDk);
        }

        public void capNhatThongTinKh(Connect conn, CustomerViewModel ctm)
        {
            String sqlKh = "update khachhang set HoTen = '" + ctm.HoTen + "',CMND = '" + ctm.CMND + "',NgheNghiep = '" + ctm.NgheNghiep + "',DiaChi = '" + ctm.DiaChi + "',DienThoai = '" + ctm.DienThoai + "',Email = '" + ctm.Email + "' where MaKhachHang = '" + ctm.MaKh + "'";
            Console.WriteLine("Ma kh = " + ctm.MaKh);
            updateRowInDataTable(conn, sqlKh);
            String sqlmctcd = "select MaChiTiet from CHITIETKIEUCAIDAT where MaKieuCaiDat = '" + Int32.Parse(ctm.TenKieuCaiDat) + "' and MaModem = '" + Convert.ToInt32(ctm.TenModem) + "'";
            int maCtkcd = getId(conn, sqlmctcd);
            String sqlMadk = "select MaDangKy from DangKy where MaKhachHang = " + ctm.MaKh;
            int maDk = getId(conn, sqlMadk);
            String sqlDk = "update dangky set DiaChiCaiDat = '" + ctm.DiaChiCaiDat + "',DiaChiHoaDon = '" + ctm.DiaChiHoaDon + "',SoLuongTaiKhoan = '" + ctm.SoLuongTaiKhoan + "',TenNguoiSuDung = '" + ctm.TenNguoiSuDung + "',MaGoiCuoc = '" + Convert.ToInt32(ctm.TenGoiCuoc) + "',TronGoi = '" + Convert.ToInt32(ctm.TronGoi) + "',MaChiTietKieuCaiDat = '" + maCtkcd + "' where MaDangky = '" + maDk + "'";
            updateRowInDataTable(conn, sqlDk);
            String sqlTinhtrang = "update hopdong set matinhtrang = " + Int32.Parse(ctm.TenTinhTrangSuDung) + " where mahopdong = " + ctm.MaHopDong;
            updateRowInDataTable(conn, sqlTinhtrang);
        }

        public void updateRowInDataTable(Connect conn, String query)
        {
            conn.getConnect();
            try
            {
                MySqlCommand cmd = new MySqlCommand(query, conn.con);
                cmd.ExecuteNonQuery();
                conn.CloseConnet();
            }
            catch
            {
                MessageBox.Show("Lỗi truy vấn ", "Error");
            }
        }

        public bool isValuesExist(Connect conn, String query, String values)
        {
            try
            {
                query += Int32.Parse(values) + "'";
            }
            catch (Exception)
            {
                query += values + "'";
            }

            return isExist(conn, query);
        }

        public bool isExist(Connect conn, String query)
        {
            bool check = false;
            MySqlDataReader reader = null;
            conn.getConnect();
            MySqlCommand cmd = new MySqlCommand(query, conn.con);
            reader = cmd.ExecuteReader();
            if (reader.Read())
            {
                check = true;
            }
            reader.Close();
            conn.CloseConnet();
            return check;
        }

        public String getTinhTrangThanhToan(Connect conn, String str, Query qr)
        {
            String queryMaKh = qr.sqlGetMaKh + str + "'";
            if (isValuesExist(conn, qr.sqlGetMaKh, str))
            {
                int maKh = getId(conn, queryMaKh);
                String query = "select TinhTrangThanhToan from dangky where MaKhachHang = '" + maKh + "'";
                return getValues(conn, query);
            }
            else
                return "";

        }

        public String getTTThanhToan(Connect conn,String query)
        {

            return null;
        }

        public DateTime getDayDk(Connect conn, int str)
        {
            DateTime d = new DateTime();
            String sqlMaDangKy = "select MaDangKy from dangky where MaKhachHang = " + str;
            int maDangKy = getId(conn, sqlMaDangKy);
            String query = "select NgayDangKy from DangKy where MaDangKy = '" + maDangKy + "'";
            MySqlDataReader reader = null;
            if (!conn.IsOpenning)
                conn.getConnect();
            MySqlCommand cmd = new MySqlCommand(query, conn.con);
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                d = reader.GetDateTime(0);
            }
            reader.Close();
            conn.CloseConnet();
            return d;
        }

        public int getMoney(Connect conn, int str)
        {
            String sqlMaDangKy = "select MaDangKy from dangky where MaKhachHang = '" + str + "'";

            int maDangKy = getId(conn, sqlMaDangKy);
            Console.WriteLine("mdky = " + maDangKy);
            String sqlMctkcd = "select MaChiTietKieuCaiDat from DangKy where MaDangKy = '" + maDangKy + "'";
            int maCtkcd = getId(conn, sqlMctkcd);
            Console.WriteLine("mchitietKCd = " + maCtkcd);
            String query = "select dongia from chitietkieucaidat where machitiet = '" + maCtkcd + "'";
            return Int32.Parse(getValues(conn, query));
        }

        public void thanhToanPhiHoaMang(Connect conn, CustomerViewModel ctm)
        {
            String sqlMaDangKy = "select MaDangKy from dangky where MaKhachHang = '" + ctm.MaKh + "'";
            int maDangKy = getId(conn, sqlMaDangKy);
            int money = getMoney(conn, ctm.MaKh);
            String query = "update dangky set phihoamang = '" + money + "',tinhtrangthanhtoan = '1' where madangky = '" + maDangKy + "'";
            updateRowInDataTable(conn, query);
            DateTime d = DateTime.Now;
            String sql = "insert into hopdong(makhachhang,matinhtrang,ngaybatdausudung) values('" + ctm.MaKh + "','" + ctm.TinhTrangSuDung + "','" + d.ToString("yyyy/MM/dd") + "')";
            inserIntoDataTable(conn, sql);
        }

        public void insertThongTinLoi(Connect conn, int maHd, String thongTinLoi)
        {
            DateTime d = DateTime.Now;
            String query = "insert into baoloi(mahopdong,ngaybaoloi,thongtinloi) values('" + maHd + "','" + d.ToString("yyyy/MM/dd HH:mm:ss") + "','" + thongTinLoi + "')";
            inserIntoDataTable(conn, query);
            String sql = "update hopdong set loikythuat = '1' where mahopdong = '" + maHd + "'";
            updateRowInDataTable(conn, sql);
        }

        public ObservableCollection<CustomerViewModel> disPlayErrorInfomation(Connect conn, String query)
        {
            ObservableCollection<CustomerViewModel> customer = new ObservableCollection<CustomerViewModel>();
            String ngayBaoLoi = "";
            String ngaySuaLoi = "";
            String thongTinLoi = "";
            String cachKhachPhuc = "";
            MySqlDataReader reader = null;
            int i = 0;
            if (!conn.IsOpenning)
                conn.getConnect();
            MySqlCommand cmd = new MySqlCommand(query, conn.con);
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                try
                {
                    ngayBaoLoi = reader.GetDateTime(0).ToString("dd/MM/yyyy");
                }
                catch (Exception)
                {
                    ngayBaoLoi = "";
                }
                try
                {
                    ngaySuaLoi = reader.GetDateTime(2).ToString("dd/MM/yyyy");
                }
                catch (Exception)
                {
                    ngaySuaLoi = "";
                }
                try
                {
                    thongTinLoi = reader.GetString(1);
                }
                catch (Exception)
                {
                    thongTinLoi = "";
                }
                try
                {
                    cachKhachPhuc = reader.GetString(3);
                }
                catch (Exception)
                {
                    cachKhachPhuc = "";
                }

                customer.Add(new CustomerViewModel
                {
                    STT = (i + 1).ToString(),
                    NgayBaoLoiString = ngayBaoLoi,
                    ThongTinLoi = thongTinLoi,
                    NgaySuaLoiString = ngaySuaLoi,
                    CachKhacPhuc = cachKhachPhuc
                });
                i++;

            }
            reader.Close();
            conn.CloseConnet();
            return customer;
        }

        public ObservableCollection<CustomerViewModel> disPlayBillInfomation(Connect conn, String query)
        {
            ObservableCollection<CustomerViewModel> customer = new ObservableCollection<CustomerViewModel>();
            String tuNgay = "";
            String denNgay = "";
            int tongTien = 0;
            String tinhTrang = "";
            MySqlDataReader reader = null;
            int i = 0;
            if (!conn.IsOpenning)
                conn.getConnect();
            MySqlCommand cmd = new MySqlCommand(query, conn.con);
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {

                try
                {
                    tuNgay = reader.GetDateTime(0).ToString("dd/MM/yyyy");
                }
                catch (Exception)
                {
                    tuNgay = "";
                }
                try
                {
                    denNgay = reader.GetDateTime(1).ToString("dd/MM/yyyy");
                }
                catch (Exception)
                {
                    denNgay = "";

                }
                try
                {
                    tongTien = reader.GetInt32(2);
                }
                catch (Exception)
                {
                    tongTien = 0;

                }

                try
                {
                    if (reader.GetInt32(3) == 1)
                        tinhTrang = "Đã thanh toán";
                    else
                        tinhTrang = "Chưa thanh toán";
                }
                catch (Exception)
                {
                    tinhTrang = "False";

                }

                customer.Add(new CustomerViewModel
                {
                    STT = (i + 1).ToString(),
                    TuNgay = tuNgay,
                    DenNgay = denNgay,
                    TongTienThanhToan = tongTien,
                    TinhTrangThanhToan = tinhTrang
                });
                i++;
            }
            reader.Close(); ;
            conn.CloseConnet();
            return customer;
        }
    }
}
